package com.b2c.repository;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.xhs.XhsOrderEntity;
import com.b2c.entity.xhs.XhsOrderItemEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class XhsOrderRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 拉取小红书店铺订单
     * @param order
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Long> pullOrder(XhsOrderEntity order)  {
        Long orderId = jdbcTemplate.queryForObject("SELECT IFNULL((select id from dc_xhs_order where orderId=? limit 1),0) id ", Long.class, order.getOrderId());
        if (orderId > 0) {
            //订单存在，更新订单状态
            jdbcTemplate.update("update dc_xhs_order set orderStatus=?,afterSalesStatus=?,cancelStatus=?," +
                            "updateTime=?,deliveryTime=?,cancelTime=?,finishTime=?,customerRemark=?,sellerRemark=?,sellerRemarkFlag=?," +
                            "expressTrackingNo=?,expressCompanyCode=? where id=?",
                        order.getOrderStatus(), order.getAfterSalesStatus(), order.getCancelStatus(),
                    order.getUpdateTime(),order.getDeliveryTime(),order.getCancelTime(),order.getFinishTime(),order.getCustomerRemark(),order.getSellerRemark(),order.getSellerRemarkFlag()
                    , order.getExpressTrackingNo(),order.getExpressCompanyCode(),orderId);

            return new ResultVo<>(EnumResultVo.DataExist, "已存在，更新状态",orderId);
        }

        /*****1、添加order*****/
        StringBuilder orderInsertSQL = new StringBuilder();
        orderInsertSQL.append("INSERT INTO dc_xhs_order");
        orderInsertSQL.append(" SET ");
        orderInsertSQL.append(" orderId=?,");
        orderInsertSQL.append(" shopType=?,");
        orderInsertSQL.append(" shopId=?,");
        orderInsertSQL.append(" orderType=?,");
        orderInsertSQL.append(" orderStatus=?,");
        orderInsertSQL.append(" afterSalesStatus=?,");
        orderInsertSQL.append(" cancelStatus=?,");
        orderInsertSQL.append(" createdTime=?,");
        orderInsertSQL.append(" paidTime=?,");
        orderInsertSQL.append(" updateTime=?,");
        orderInsertSQL.append(" deliveryTime=?,");
        orderInsertSQL.append(" cancelTime=?,");
        orderInsertSQL.append(" finishTime=?,");
        orderInsertSQL.append(" promiseLastDeliveryTime=?,");
        orderInsertSQL.append(" customerRemark=?,");
        orderInsertSQL.append(" sellerRemark=?, ");
        orderInsertSQL.append(" sellerRemarkFlag=?,");
        orderInsertSQL.append(" presaleDeliveryStartTime=?,");
        orderInsertSQL.append(" presaleDeliveryEndTime=?,");
        orderInsertSQL.append(" originalPackageId=?,");
        orderInsertSQL.append(" totalPayAmount=?,");
        orderInsertSQL.append(" totalShippingFree=?,");
        orderInsertSQL.append(" expressTrackingNo=?,");
        orderInsertSQL.append(" expressCompanyCode=?,");
        orderInsertSQL.append(" openAddressId=?,");
        orderInsertSQL.append(" province=?,");
        orderInsertSQL.append(" city=?,");
        orderInsertSQL.append(" district=?");
        try {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, order.getOrderId());
                    ps.setInt(2, order.getShopType());
                    ps.setInt(3, order.getShopId());
                    ps.setInt(4, order.getOrderType());
                    ps.setInt(5, order.getOrderStatus());
                    ps.setInt(6, order.getAfterSalesStatus());
                    ps.setInt(7, order.getCancelStatus());
                    ps.setLong(8, order.getCreatedTime());
                    ps.setLong(9, order.getPaidTime());
                    ps.setLong(10, order.getUpdateTime());
                    ps.setLong(11, order.getDeliveryTime());
                    ps.setLong(12, order.getCancelTime());
                    ps.setLong(13, order.getFinishTime());
                    ps.setLong(14, order.getPromiseLastDeliveryTime());
                    ps.setString(15, order.getCustomerRemark());
                    ps.setString(16, order.getSellerRemark());
                    ps.setInt(17, order.getSellerRemarkFlag());
                    ps.setLong(18, order.getPresaleDeliveryStartTime());
                    ps.setLong(19, order.getPresaleDeliveryEndTime());
                    ps.setString(20, order.getOriginalPackageId());
                    ps.setInt(21, order.getTotalPayAmount());
                    ps.setInt(22, order.getTotalShippingFree());
                    ps.setString(23, order.getExpressTrackingNo());//order.getShopCouponAmount()
                    ps.setString(24, order.getExpressCompanyCode());
                    ps.setString(25, order.getOpenAddressId());
                    ps.setString(26, order.getProvince());
                    ps.setString(27, order.getCity());
                    ps.setString(28, order.getDistrict());
                    return ps;
                }
            }, keyHolder);

            Long orderIdNew = keyHolder.getKey().longValue();

            /*****1、添加dc_douyin_orders_items*****/
            String itemSQL = "INSERT INTO dc_xhs_order_item (orderId,itemId,itemName,erpcode,itemSpec,itemImage,quantity,totalPaidAmount,totalMerchantDiscount,totalRedDiscount,itemTag)" +
                    " VALUE (?,?,?,?,?,?,?,?,?,?,?)";

            for (var item : order.getItems()) {

                jdbcTemplate.update(itemSQL, orderIdNew, item.getItemId(), item.getItemName() , item.getErpCode(), item.getItemSpec(), item.getItemImage(), item.getQuantity(), item.getTotalPaidAmount(),
                         item.getTotalMerchantDiscount(), item.getTotalRedDiscount(), item.getItemTag());

            }
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功",orderIdNew);
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "异常：" + e.getMessage());
        }
    }

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    @Transactional
    public PagingResponse<XhsOrderEntity> getOrderList(Integer shopId, Integer pageIndex, Integer pageSize, String orderNum, Integer startTime, Integer endTime, Integer state, String logisticsCode) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS  A.*,r.receiver,r.phone,r.address ");
        sb.append(" FROM dc_xhs_order A ");
        sb.append(" LEFT JOIN dc_xhs_order_receiver r ON r.orderId=A.id ");
        sb.append(" WHERE A.shopId=? ");
        List<Object> params = new ArrayList<>();
        params.add(shopId);

        if (!StringUtils.isEmpty(orderNum)) {
            sb.append("AND A.orderId = ? ");
            params.add(orderNum);
        }

        if (!StringUtils.isEmpty(logisticsCode)) {
            sb.append("AND A.expressTrackingNo=? ");
            params.add(logisticsCode.trim());
        }

        if (!StringUtils.isEmpty(startTime)) {
            sb.append("AND A.createdTime > ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime)) {
            sb.append("AND A.createdTime <= ? ");
            params.add(endTime);
        }

        if (state!=null) {
            sb.append("AND  A.orderStatus = ?  ");
            params.add(state);
            if(state.intValue() == 4){
                sb.append(" AND A.afterSalesStatus=1 AND A.cancelStatus=0 ");
            }
        }
        sb.append(" ORDER BY A.createdTime DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<XhsOrderEntity> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(XhsOrderEntity.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();

        StringBuilder itemSql = new StringBuilder();
        itemSql.append("SELECT item.* ");

        itemSql.append("from dc_xhs_order_item item where orderId=? ");

        lists.forEach(list -> list.setItems(jdbcTemplate.query(itemSql.toString(), new BeanPropertyRowMapper<>(XhsOrderItemEntity.class), list.getId())));
        return new PagingResponse<>(pageIndex, pageSize, totalSize, lists);
    }

    public XhsOrderEntity getOrderDetailById(Long id) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS  A.*,r.receiver,r.phone,r.address ");
        sb.append(" FROM dc_xhs_order A ");
        sb.append(" LEFT JOIN dc_xhs_order_receiver r ON r.orderId=A.id ");
        sb.append(" WHERE A.id=? ");
        try {
            var order = jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(XhsOrderEntity.class), id);

            StringBuilder itemSql = new StringBuilder();
            itemSql.append("SELECT item.* ");

            itemSql.append("from dc_xhs_order_item item where orderId=? ");

            order.setItems(jdbcTemplate.query(itemSql.toString(), new BeanPropertyRowMapper<>(XhsOrderItemEntity.class), order.getId()));
            return order;

        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 更新收获地址
     * @param id 订单id
     * @param
     * @param province
     * @param city
     * @param district
     * @param address
     * @param receiverName
     * @param receiverPhone
     * @return
     */
    public ResultVo<Long> pullOrderAddress(Long id, String province, String city, String district, String address, String receiverName, String receiverPhone) {
        var count = jdbcTemplate.queryForObject("SELECT count(0) FROM dc_xhs_order_receiver WHERE orderId=?", Integer.class, id);
        if (count > 0) {
            //删除旧的
            jdbcTemplate.update("DELETE FROM dc_xhs_order_receiver WHERE orderId=?",id);
        }
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO dc_xhs_order_receiver (orderId,receiver,phone,province,city,district,address) ");
        sb.append(" VALUE (?,?,?,?,?,?,?)");
        jdbcTemplate.update(sb.toString(), id, receiverName, receiverPhone, province, city, district, address);

        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    public ResultVo<Integer> updOrderRemark(Long id, String remark) {
        String sql= "UPDATE dc_xhs_order SET sellerRemark=? WHERE id=?";
        jdbcTemplate.update(sql,remark,id);
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }
}
